{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 201,
   "id": "7e7d263e",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 202,
   "id": "c6e7ebf6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>religion</th>\n",
       "      <th>&lt;10k</th>\n",
       "      <th>&lt;15k</th>\n",
       "      <th>&lt;20k</th>\n",
       "      <th>&lt;25k</th>\n",
       "      <th>&gt;25k</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>22</td>\n",
       "      <td>33</td>\n",
       "      <td>24</td>\n",
       "      <td>45</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>athestic</td>\n",
       "      <td>25</td>\n",
       "      <td>37</td>\n",
       "      <td>44</td>\n",
       "      <td>45</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>15</td>\n",
       "      <td>27</td>\n",
       "      <td>44</td>\n",
       "      <td>55</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    religion  <10k  <15k  <20k  <25k  >25k\n",
       "0   agnostic    22    33    24    45    55\n",
       "1   athestic    25    37    44    45    75\n",
       "2  cathostic    15    27    44    55    65"
      ]
     },
     "execution_count": 202,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = [['agnostic', 22,33,24,45,55],['athestic', 25,37,44,45,75],['cathostic', 15,27,44,55,65]]\n",
    "df = pd.DataFrame(data, index=[0,1,2], columns=['religion', '<10k', '<15k', '<20k', '<25k', '>25k'])\n",
    "df\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "c87e653c",
   "metadata": {},
   "source": [
    "# melt 函数参数\n",
    "融合操作\n",
    "- frame:需要处理的数据\n",
    "- id_vars:保持原样的数据列\n",
    "- value_vars: 需要被转换成变量值的数据列\n",
    "- var_name: 转换后变量的列名\n",
    "- value_name: 数值变量的列名"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 203,
   "id": "1411c299",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     religion income  freq\n",
       "0    agnostic   <10k    22\n",
       "3    agnostic   <15k    33\n",
       "6    agnostic   <20k    24\n",
       "9    agnostic   <25k    45\n",
       "12   agnostic   >25k    55\n",
       "1    athestic   <10k    25\n",
       "4    athestic   <15k    37\n",
       "7    athestic   <20k    44\n",
       "10   athestic   <25k    45\n",
       "13   athestic   >25k    75\n",
       "2   cathostic   <10k    15\n",
       "5   cathostic   <15k    27\n",
       "8   cathostic   <20k    44\n",
       "11  cathostic   <25k    55\n",
       "14  cathostic   >25k    65"
      ]
     },
     "execution_count": 203,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "format_df=pd.melt(df,id_vars=['religion'],var_name='income', value_name='freq')\n",
    "format_df=format_df.sort_values(by=['religion'])\n",
    "format_df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "626ff892",
   "metadata": {},
   "source": [
    "# stack 进行堆叠操作\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 204,
   "id": "77a7641f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>religion</th>\n",
       "      <th>income</th>\n",
       "      <th>freq</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>22</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>33</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>agnostic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>45</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>athestic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;10k</td>\n",
       "      <td>15</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;15k</td>\n",
       "      <td>27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;20k</td>\n",
       "      <td>44</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&lt;25k</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>cathostic</td>\n",
       "      <td>&gt;25k</td>\n",
       "      <td>65</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "     religion income  freq\n",
       "0    agnostic   <10k    22\n",
       "1    agnostic   <15k    33\n",
       "2    agnostic   <20k    24\n",
       "3    agnostic   <25k    45\n",
       "4    agnostic   >25k    55\n",
       "5    athestic   <10k    25\n",
       "6    athestic   <15k    37\n",
       "7    athestic   <20k    44\n",
       "8    athestic   <25k    45\n",
       "9    athestic   >25k    75\n",
       "10  cathostic   <10k    15\n",
       "11  cathostic   <15k    27\n",
       "12  cathostic   <20k    44\n",
       "13  cathostic   <25k    55\n",
       "14  cathostic   >25k    65"
      ]
     },
     "execution_count": 204,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 将religion设置为行索引\n",
    "format_df = df.set_index('religion') \n",
    "# 将所有列堆叠\n",
    "format_df = format_df.stack() \n",
    "# 将二级索引明修改为income\n",
    "format_df.index=format_df.index.rename('income', level=1) \n",
    "format_df.name = 'freq'\n",
    "format_df=format_df.reset_index()\n",
    "format_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 205,
   "id": "0ec27818",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "range(0, 10)"
      ]
     },
     "execution_count": 205,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import math\n",
    "range(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 206,
   "id": "bb30dd90",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['year', 'artist', 'track', 'time', 'genere', 'date.entered', 'date.peaked', 'x1nd.week', 'x2nd.week', 'x3nd.week', 'x4nd.week', 'x5nd.week', 'x6nd.week', 'x7nd.week', 'x8nd.week', 'x9nd.week', 'x10nd.week']\n"
     ]
    }
   ],
   "source": [
    "# 构造一个相对混乱的表格数据\n",
    "import math\n",
    "import numpy as np\n",
    "def gen_random_week():\n",
    "    str = ''\n",
    "    list=[]\n",
    "    for i in range(10):\n",
    "        s = \"{}{}{}\".format('x', i+1, 'nd.week') \n",
    "        list.append(s)\n",
    "    return list\n",
    "\n",
    "def_column = ['year','artist','track', 'time', 'genere', 'date.entered', 'date.peaked', ]\n",
    "def_column.extend(gen_random_week())\n",
    "print(def_column, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 275,
   "id": "d5c662be",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genere</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>x1nd.week</th>\n",
       "      <th>x2nd.week</th>\n",
       "      <th>x3nd.week</th>\n",
       "      <th>x4nd.week</th>\n",
       "      <th>x5nd.week</th>\n",
       "      <th>x6nd.week</th>\n",
       "      <th>x7nd.week</th>\n",
       "      <th>x8nd.week</th>\n",
       "      <th>x9nd.week</th>\n",
       "      <th>x10nd.week</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  year artist track time genere date.entered date.peaked x1nd.week x2nd.week  \\\n",
       "0  NaN    NaN   NaN  NaN    NaN          NaN         NaN       NaN       NaN   \n",
       "1  NaN    NaN   NaN  NaN    NaN          NaN         NaN       NaN       NaN   \n",
       "2  NaN    NaN   NaN  NaN    NaN          NaN         NaN       NaN       NaN   \n",
       "3  NaN    NaN   NaN  NaN    NaN          NaN         NaN       NaN       NaN   \n",
       "4  NaN    NaN   NaN  NaN    NaN          NaN         NaN       NaN       NaN   \n",
       "\n",
       "  x3nd.week x4nd.week x5nd.week x6nd.week x7nd.week x8nd.week x9nd.week  \\\n",
       "0       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "1       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "2       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "3       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "4       NaN       NaN       NaN       NaN       NaN       NaN       NaN   \n",
       "\n",
       "  x10nd.week  \n",
       "0        NaN  \n",
       "1        NaN  \n",
       "2        NaN  \n",
       "3        NaN  \n",
       "4        NaN  "
      ]
     },
     "execution_count": 275,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame([],index=[np.arange(5)], columns=def_column)\n",
    "#df = df.fillna(0)\n",
    "#print(df)\n",
    "#print(df.drop_duplicates())\n",
    "#df.duplicated()\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 279,
   "id": "43c8efbf",
   "metadata": {},
   "outputs": [
    {
     "ename": "AttributeError",
     "evalue": "'DataFrame' object has no attribute 'density'",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mAttributeError\u001b[0m                            Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_12308\\2315323419.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[0;32m      1\u001b[0m \u001b[1;31m# 处理稀疏数据\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m      2\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m----> 3\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mdensity\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36m__getattr__\u001b[1;34m(self, name)\u001b[0m\n\u001b[0;32m   5573\u001b[0m         ):\n\u001b[0;32m   5574\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m[\u001b[0m\u001b[0mname\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5575\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mobject\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__getattribute__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5576\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5577\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0m__setattr__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mname\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mstr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[1;32mNone\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;31mAttributeError\u001b[0m: 'DataFrame' object has no attribute 'density'"
     ]
    }
   ],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": 208,
   "id": "56c51c3c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genere</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>week</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 02:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 03:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 04:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 05:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 06:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 07:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 08:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 09:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 10:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 11:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 13:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 14:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 15:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 16:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 17:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 18:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 19:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 20:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 21:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 22:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 23:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 02:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 03:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 04:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 05:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 06:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 07:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 08:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 09:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 10:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 11:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 13:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 14:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 15:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 16:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 17:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 18:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 19:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 20:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 21:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 22:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 23:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-03 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-03 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year artist track                 time genere date.entered date.peaked  \\\n",
       "0     1      2     3                    4      5            6           7   \n",
       "1   NaN    NaN   NaN  2023-01-01 01:00:00    NaN          NaN         NaN   \n",
       "2   NaN    NaN   NaN  2023-01-01 02:00:00    NaN          NaN         NaN   \n",
       "3   NaN    NaN   NaN  2023-01-01 03:00:00    NaN          NaN         NaN   \n",
       "4   NaN    NaN   NaN  2023-01-01 04:00:00    NaN          NaN         NaN   \n",
       "5   NaN    NaN   NaN  2023-01-01 05:00:00    NaN          NaN         NaN   \n",
       "6   NaN    NaN   NaN  2023-01-01 06:00:00    NaN          NaN         NaN   \n",
       "7   NaN    NaN   NaN  2023-01-01 07:00:00    NaN          NaN         NaN   \n",
       "8   NaN    NaN   NaN  2023-01-01 08:00:00    NaN          NaN         NaN   \n",
       "9   NaN    NaN   NaN  2023-01-01 09:00:00    NaN          NaN         NaN   \n",
       "10  NaN    NaN   NaN  2023-01-01 10:00:00    NaN          NaN         NaN   \n",
       "11  NaN    NaN   NaN  2023-01-01 11:00:00    NaN          NaN         NaN   \n",
       "12  NaN    NaN   NaN  2023-01-01 12:00:00    NaN          NaN         NaN   \n",
       "13  NaN    NaN   NaN  2023-01-01 13:00:00    NaN          NaN         NaN   \n",
       "14  NaN    NaN   NaN  2023-01-01 14:00:00    NaN          NaN         NaN   \n",
       "15  NaN    NaN   NaN  2023-01-01 15:00:00    NaN          NaN         NaN   \n",
       "16  NaN    NaN   NaN  2023-01-01 16:00:00    NaN          NaN         NaN   \n",
       "17  NaN    NaN   NaN  2023-01-01 17:00:00    NaN          NaN         NaN   \n",
       "18  NaN    NaN   NaN  2023-01-01 18:00:00    NaN          NaN         NaN   \n",
       "19  NaN    NaN   NaN  2023-01-01 19:00:00    NaN          NaN         NaN   \n",
       "20  NaN    NaN   NaN  2023-01-01 20:00:00    NaN          NaN         NaN   \n",
       "21  NaN    NaN   NaN  2023-01-01 21:00:00    NaN          NaN         NaN   \n",
       "22  NaN    NaN   NaN  2023-01-01 22:00:00    NaN          NaN         NaN   \n",
       "23  NaN    NaN   NaN  2023-01-01 23:00:00    NaN          NaN         NaN   \n",
       "24  NaN    NaN   NaN  2023-01-02 00:00:00    NaN          NaN         NaN   \n",
       "25  NaN    NaN   NaN  2023-01-02 01:00:00    NaN          NaN         NaN   \n",
       "26  NaN    NaN   NaN  2023-01-02 02:00:00    NaN          NaN         NaN   \n",
       "27  NaN    NaN   NaN  2023-01-02 03:00:00    NaN          NaN         NaN   \n",
       "28  NaN    NaN   NaN  2023-01-02 04:00:00    NaN          NaN         NaN   \n",
       "29  NaN    NaN   NaN  2023-01-02 05:00:00    NaN          NaN         NaN   \n",
       "30  NaN    NaN   NaN  2023-01-02 06:00:00    NaN          NaN         NaN   \n",
       "31  NaN    NaN   NaN  2023-01-02 07:00:00    NaN          NaN         NaN   \n",
       "32  NaN    NaN   NaN  2023-01-02 08:00:00    NaN          NaN         NaN   \n",
       "33  NaN    NaN   NaN  2023-01-02 09:00:00    NaN          NaN         NaN   \n",
       "34  NaN    NaN   NaN  2023-01-02 10:00:00    NaN          NaN         NaN   \n",
       "35  NaN    NaN   NaN  2023-01-02 11:00:00    NaN          NaN         NaN   \n",
       "36  NaN    NaN   NaN  2023-01-02 12:00:00    NaN          NaN         NaN   \n",
       "37  NaN    NaN   NaN  2023-01-02 13:00:00    NaN          NaN         NaN   \n",
       "38  NaN    NaN   NaN  2023-01-02 14:00:00    NaN          NaN         NaN   \n",
       "39  NaN    NaN   NaN  2023-01-02 15:00:00    NaN          NaN         NaN   \n",
       "40  NaN    NaN   NaN  2023-01-02 16:00:00    NaN          NaN         NaN   \n",
       "41  NaN    NaN   NaN  2023-01-02 17:00:00    NaN          NaN         NaN   \n",
       "42  NaN    NaN   NaN  2023-01-02 18:00:00    NaN          NaN         NaN   \n",
       "43  NaN    NaN   NaN  2023-01-02 19:00:00    NaN          NaN         NaN   \n",
       "44  NaN    NaN   NaN  2023-01-02 20:00:00    NaN          NaN         NaN   \n",
       "45  NaN    NaN   NaN  2023-01-02 21:00:00    NaN          NaN         NaN   \n",
       "46  NaN    NaN   NaN  2023-01-02 22:00:00    NaN          NaN         NaN   \n",
       "47  NaN    NaN   NaN  2023-01-02 23:00:00    NaN          NaN         NaN   \n",
       "48  NaN    NaN   NaN  2023-01-03 00:00:00    NaN          NaN         NaN   \n",
       "49  NaN    NaN   NaN  2023-01-03 01:00:00    NaN          NaN         NaN   \n",
       "\n",
       "          week rank  \n",
       "0            8    9  \n",
       "1    x1nd.week  NaN  \n",
       "2    x1nd.week  NaN  \n",
       "3    x1nd.week  NaN  \n",
       "4    x1nd.week  NaN  \n",
       "5    x2nd.week  NaN  \n",
       "6    x2nd.week  NaN  \n",
       "7    x2nd.week  NaN  \n",
       "8    x2nd.week  NaN  \n",
       "9    x2nd.week  NaN  \n",
       "10   x3nd.week  NaN  \n",
       "11   x3nd.week  NaN  \n",
       "12   x3nd.week  NaN  \n",
       "13   x3nd.week  NaN  \n",
       "14   x3nd.week  NaN  \n",
       "15   x4nd.week  NaN  \n",
       "16   x4nd.week  NaN  \n",
       "17   x4nd.week  NaN  \n",
       "18   x4nd.week  NaN  \n",
       "19   x4nd.week  NaN  \n",
       "20   x5nd.week  NaN  \n",
       "21   x5nd.week  NaN  \n",
       "22   x5nd.week  NaN  \n",
       "23   x5nd.week  NaN  \n",
       "24   x5nd.week  NaN  \n",
       "25   x6nd.week  NaN  \n",
       "26   x6nd.week  NaN  \n",
       "27   x6nd.week  NaN  \n",
       "28   x6nd.week  NaN  \n",
       "29   x6nd.week  NaN  \n",
       "30   x7nd.week  NaN  \n",
       "31   x7nd.week  NaN  \n",
       "32   x7nd.week  NaN  \n",
       "33   x7nd.week  NaN  \n",
       "34   x7nd.week  NaN  \n",
       "35   x8nd.week  NaN  \n",
       "36   x8nd.week  NaN  \n",
       "37   x8nd.week  NaN  \n",
       "38   x8nd.week  NaN  \n",
       "39   x8nd.week  NaN  \n",
       "40   x9nd.week  NaN  \n",
       "41   x9nd.week  NaN  \n",
       "42   x9nd.week  NaN  \n",
       "43   x9nd.week  NaN  \n",
       "44   x9nd.week  NaN  \n",
       "45  x10nd.week  NaN  \n",
       "46  x10nd.week  NaN  \n",
       "47  x10nd.week  NaN  \n",
       "48  x10nd.week  NaN  \n",
       "49  x10nd.week  NaN  "
      ]
     },
     "execution_count": 208,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "id_vars = ['year','artist','track', 'time', 'genere', 'date.entered', 'date.peaked']\n",
    "df=pd.melt(frame=df,id_vars=id_vars, var_name=\"week\", value_name=\"rank\")\n",
    "df.loc[0] = np.arange(1,10)\n",
    "fill_value = pd.Series(pd.date_range(start=pd.Timestamp('2023-01-01'), periods=50, freq='H'))\n",
    "#print(fill_value)\n",
    "df_c=df['time'].fillna(fill_value) \n",
    "df['time'] = df_c\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 209,
   "id": "04c37267",
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "cannot convert float NaN to integer",
     "output_type": "error",
     "traceback": [
      "\u001b[1;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[1;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[1;32m~\\AppData\\Local\\Temp\\ipykernel_12308\\1956986923.py\u001b[0m in \u001b[0;36m<module>\u001b[1;34m\u001b[0m\n\u001b[1;32m----> 1\u001b[1;33m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"week\"\u001b[0m\u001b[1;33m]\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mdf\u001b[0m\u001b[1;33m[\u001b[0m\u001b[1;34m\"week\"\u001b[0m\u001b[1;33m]\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mstr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mextract\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m'(\\d+)'\u001b[0m\u001b[1;33m,\u001b[0m\u001b[0mexpand\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;32mFalse\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mint\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\generic.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m   5910\u001b[0m         \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5911\u001b[0m             \u001b[1;31m# else, only a single dtype is given\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 5912\u001b[1;33m             \u001b[0mnew_data\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_mgr\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   5913\u001b[0m             \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m_constructor\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnew_data\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0m__finalize__\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mmethod\u001b[0m\u001b[1;33m=\u001b[0m\u001b[1;34m\"astype\"\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   5914\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m    417\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    418\u001b[0m     \u001b[1;32mdef\u001b[0m \u001b[0mastype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mself\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mT\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mbool\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;32mFalse\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m:\u001b[0m \u001b[0mstr\u001b[0m \u001b[1;33m=\u001b[0m \u001b[1;34m\"raise\"\u001b[0m\u001b[1;33m)\u001b[0m \u001b[1;33m->\u001b[0m \u001b[0mT\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 419\u001b[1;33m         \u001b[1;32mreturn\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;34m\"astype\"\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    420\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    421\u001b[0m     def convert(\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\internals\\managers.py\u001b[0m in \u001b[0;36mapply\u001b[1;34m(self, f, align_keys, ignore_failures, **kwargs)\u001b[0m\n\u001b[0;32m    302\u001b[0m                     \u001b[0mapplied\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mb\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mapply\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mf\u001b[0m\u001b[1;33m,\u001b[0m \u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    303\u001b[0m                 \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 304\u001b[1;33m                     \u001b[0mapplied\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mgetattr\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mb\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mf\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m(\u001b[0m\u001b[1;33m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    305\u001b[0m             \u001b[1;32mexcept\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mTypeError\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mNotImplementedError\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    306\u001b[0m                 \u001b[1;32mif\u001b[0m \u001b[1;32mnot\u001b[0m \u001b[0mignore_failures\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\internals\\blocks.py\u001b[0m in \u001b[0;36mastype\u001b[1;34m(self, dtype, copy, errors)\u001b[0m\n\u001b[0;32m    578\u001b[0m         \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mself\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    579\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m--> 580\u001b[1;33m         \u001b[0mnew_values\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mastype_array_safe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m    581\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m    582\u001b[0m         \u001b[0mnew_values\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mmaybe_coerce_values\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mnew_values\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py\u001b[0m in \u001b[0;36mastype_array_safe\u001b[1;34m(values, dtype, copy, errors)\u001b[0m\n\u001b[0;32m   1290\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1291\u001b[0m     \u001b[1;32mtry\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1292\u001b[1;33m         \u001b[0mnew_values\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mastype_array\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1293\u001b[0m     \u001b[1;32mexcept\u001b[0m \u001b[1;33m(\u001b[0m\u001b[0mValueError\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1294\u001b[0m         \u001b[1;31m# e.g. astype_nansafe can fail on object-dtype of strings\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py\u001b[0m in \u001b[0;36mastype_array\u001b[1;34m(values, dtype, copy)\u001b[0m\n\u001b[0;32m   1235\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1236\u001b[0m     \u001b[1;32melse\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1237\u001b[1;33m         \u001b[0mvalues\u001b[0m \u001b[1;33m=\u001b[0m \u001b[0mastype_nansafe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mcopy\u001b[0m\u001b[1;33m=\u001b[0m\u001b[0mcopy\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1238\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1239\u001b[0m     \u001b[1;31m# in pandas we don't store numpy str dtypes, so convert to object\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\core\\dtypes\\cast.py\u001b[0m in \u001b[0;36mastype_nansafe\u001b[1;34m(arr, dtype, copy, skipna)\u001b[0m\n\u001b[0;32m   1152\u001b[0m         \u001b[1;31m# work around NumPy brokenness, #1987\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1153\u001b[0m         \u001b[1;32mif\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0missubdtype\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0mdtype\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mtype\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mnp\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0minteger\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m:\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[1;32m-> 1154\u001b[1;33m             \u001b[1;32mreturn\u001b[0m \u001b[0mlib\u001b[0m\u001b[1;33m.\u001b[0m\u001b[0mastype_intsafe\u001b[0m\u001b[1;33m(\u001b[0m\u001b[0marr\u001b[0m\u001b[1;33m,\u001b[0m \u001b[0mdtype\u001b[0m\u001b[1;33m)\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0m\u001b[0;32m   1155\u001b[0m \u001b[1;33m\u001b[0m\u001b[0m\n\u001b[0;32m   1156\u001b[0m         \u001b[1;31m# if we have a datetime/timedelta array of objects\u001b[0m\u001b[1;33m\u001b[0m\u001b[1;33m\u001b[0m\u001b[0m\n",
      "\u001b[1;32mE:\\python\\anaconda\\lib\\site-packages\\pandas\\_libs\\lib.pyx\u001b[0m in \u001b[0;36mpandas._libs.lib.astype_intsafe\u001b[1;34m()\u001b[0m\n",
      "\u001b[1;31mValueError\u001b[0m: cannot convert float NaN to integer"
     ]
    }
   ],
   "source": [
    "df[\"week\"] = df[\"week\"].str.extract('(\\d+)',expand=False).astype(int)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 210,
   "id": "a9f20435",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>year</th>\n",
       "      <th>artist</th>\n",
       "      <th>track</th>\n",
       "      <th>time</th>\n",
       "      <th>genere</th>\n",
       "      <th>date.entered</th>\n",
       "      <th>date.peaked</th>\n",
       "      <th>week</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 02:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 03:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 04:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x1nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 05:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 06:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 07:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 08:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 09:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x2nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 10:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 11:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 13:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 14:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x3nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 15:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 16:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 17:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 18:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 19:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x4nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 20:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 21:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 22:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-01 23:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x5nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 02:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 03:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 04:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>29</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 05:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x6nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 06:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 07:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 08:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>33</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 09:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 10:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x7nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 11:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 12:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 13:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 14:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 15:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x8nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>40</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 16:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 17:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 18:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>43</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 19:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>44</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 20:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x9nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>45</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 21:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>46</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 22:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-02 23:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-03 00:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2023-01-03 01:00:00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x10nd.week</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   year artist track                 time genere date.entered date.peaked  \\\n",
       "0     1      2     3                    4      5            6           7   \n",
       "1   NaN    NaN   NaN  2023-01-01 01:00:00    NaN          NaN         NaN   \n",
       "2   NaN    NaN   NaN  2023-01-01 02:00:00    NaN          NaN         NaN   \n",
       "3   NaN    NaN   NaN  2023-01-01 03:00:00    NaN          NaN         NaN   \n",
       "4   NaN    NaN   NaN  2023-01-01 04:00:00    NaN          NaN         NaN   \n",
       "5   NaN    NaN   NaN  2023-01-01 05:00:00    NaN          NaN         NaN   \n",
       "6   NaN    NaN   NaN  2023-01-01 06:00:00    NaN          NaN         NaN   \n",
       "7   NaN    NaN   NaN  2023-01-01 07:00:00    NaN          NaN         NaN   \n",
       "8   NaN    NaN   NaN  2023-01-01 08:00:00    NaN          NaN         NaN   \n",
       "9   NaN    NaN   NaN  2023-01-01 09:00:00    NaN          NaN         NaN   \n",
       "10  NaN    NaN   NaN  2023-01-01 10:00:00    NaN          NaN         NaN   \n",
       "11  NaN    NaN   NaN  2023-01-01 11:00:00    NaN          NaN         NaN   \n",
       "12  NaN    NaN   NaN  2023-01-01 12:00:00    NaN          NaN         NaN   \n",
       "13  NaN    NaN   NaN  2023-01-01 13:00:00    NaN          NaN         NaN   \n",
       "14  NaN    NaN   NaN  2023-01-01 14:00:00    NaN          NaN         NaN   \n",
       "15  NaN    NaN   NaN  2023-01-01 15:00:00    NaN          NaN         NaN   \n",
       "16  NaN    NaN   NaN  2023-01-01 16:00:00    NaN          NaN         NaN   \n",
       "17  NaN    NaN   NaN  2023-01-01 17:00:00    NaN          NaN         NaN   \n",
       "18  NaN    NaN   NaN  2023-01-01 18:00:00    NaN          NaN         NaN   \n",
       "19  NaN    NaN   NaN  2023-01-01 19:00:00    NaN          NaN         NaN   \n",
       "20  NaN    NaN   NaN  2023-01-01 20:00:00    NaN          NaN         NaN   \n",
       "21  NaN    NaN   NaN  2023-01-01 21:00:00    NaN          NaN         NaN   \n",
       "22  NaN    NaN   NaN  2023-01-01 22:00:00    NaN          NaN         NaN   \n",
       "23  NaN    NaN   NaN  2023-01-01 23:00:00    NaN          NaN         NaN   \n",
       "24  NaN    NaN   NaN  2023-01-02 00:00:00    NaN          NaN         NaN   \n",
       "25  NaN    NaN   NaN  2023-01-02 01:00:00    NaN          NaN         NaN   \n",
       "26  NaN    NaN   NaN  2023-01-02 02:00:00    NaN          NaN         NaN   \n",
       "27  NaN    NaN   NaN  2023-01-02 03:00:00    NaN          NaN         NaN   \n",
       "28  NaN    NaN   NaN  2023-01-02 04:00:00    NaN          NaN         NaN   \n",
       "29  NaN    NaN   NaN  2023-01-02 05:00:00    NaN          NaN         NaN   \n",
       "30  NaN    NaN   NaN  2023-01-02 06:00:00    NaN          NaN         NaN   \n",
       "31  NaN    NaN   NaN  2023-01-02 07:00:00    NaN          NaN         NaN   \n",
       "32  NaN    NaN   NaN  2023-01-02 08:00:00    NaN          NaN         NaN   \n",
       "33  NaN    NaN   NaN  2023-01-02 09:00:00    NaN          NaN         NaN   \n",
       "34  NaN    NaN   NaN  2023-01-02 10:00:00    NaN          NaN         NaN   \n",
       "35  NaN    NaN   NaN  2023-01-02 11:00:00    NaN          NaN         NaN   \n",
       "36  NaN    NaN   NaN  2023-01-02 12:00:00    NaN          NaN         NaN   \n",
       "37  NaN    NaN   NaN  2023-01-02 13:00:00    NaN          NaN         NaN   \n",
       "38  NaN    NaN   NaN  2023-01-02 14:00:00    NaN          NaN         NaN   \n",
       "39  NaN    NaN   NaN  2023-01-02 15:00:00    NaN          NaN         NaN   \n",
       "40  NaN    NaN   NaN  2023-01-02 16:00:00    NaN          NaN         NaN   \n",
       "41  NaN    NaN   NaN  2023-01-02 17:00:00    NaN          NaN         NaN   \n",
       "42  NaN    NaN   NaN  2023-01-02 18:00:00    NaN          NaN         NaN   \n",
       "43  NaN    NaN   NaN  2023-01-02 19:00:00    NaN          NaN         NaN   \n",
       "44  NaN    NaN   NaN  2023-01-02 20:00:00    NaN          NaN         NaN   \n",
       "45  NaN    NaN   NaN  2023-01-02 21:00:00    NaN          NaN         NaN   \n",
       "46  NaN    NaN   NaN  2023-01-02 22:00:00    NaN          NaN         NaN   \n",
       "47  NaN    NaN   NaN  2023-01-02 23:00:00    NaN          NaN         NaN   \n",
       "48  NaN    NaN   NaN  2023-01-03 00:00:00    NaN          NaN         NaN   \n",
       "49  NaN    NaN   NaN  2023-01-03 01:00:00    NaN          NaN         NaN   \n",
       "\n",
       "          week rank  \n",
       "0            8    9  \n",
       "1    x1nd.week  NaN  \n",
       "2    x1nd.week  NaN  \n",
       "3    x1nd.week  NaN  \n",
       "4    x1nd.week  NaN  \n",
       "5    x2nd.week  NaN  \n",
       "6    x2nd.week  NaN  \n",
       "7    x2nd.week  NaN  \n",
       "8    x2nd.week  NaN  \n",
       "9    x2nd.week  NaN  \n",
       "10   x3nd.week  NaN  \n",
       "11   x3nd.week  NaN  \n",
       "12   x3nd.week  NaN  \n",
       "13   x3nd.week  NaN  \n",
       "14   x3nd.week  NaN  \n",
       "15   x4nd.week  NaN  \n",
       "16   x4nd.week  NaN  \n",
       "17   x4nd.week  NaN  \n",
       "18   x4nd.week  NaN  \n",
       "19   x4nd.week  NaN  \n",
       "20   x5nd.week  NaN  \n",
       "21   x5nd.week  NaN  \n",
       "22   x5nd.week  NaN  \n",
       "23   x5nd.week  NaN  \n",
       "24   x5nd.week  NaN  \n",
       "25   x6nd.week  NaN  \n",
       "26   x6nd.week  NaN  \n",
       "27   x6nd.week  NaN  \n",
       "28   x6nd.week  NaN  \n",
       "29   x6nd.week  NaN  \n",
       "30   x7nd.week  NaN  \n",
       "31   x7nd.week  NaN  \n",
       "32   x7nd.week  NaN  \n",
       "33   x7nd.week  NaN  \n",
       "34   x7nd.week  NaN  \n",
       "35   x8nd.week  NaN  \n",
       "36   x8nd.week  NaN  \n",
       "37   x8nd.week  NaN  \n",
       "38   x8nd.week  NaN  \n",
       "39   x8nd.week  NaN  \n",
       "40   x9nd.week  NaN  \n",
       "41   x9nd.week  NaN  \n",
       "42   x9nd.week  NaN  \n",
       "43   x9nd.week  NaN  \n",
       "44   x9nd.week  NaN  \n",
       "45  x10nd.week  NaN  \n",
       "46  x10nd.week  NaN  \n",
       "47  x10nd.week  NaN  \n",
       "48  x10nd.week  NaN  \n",
       "49  x10nd.week  NaN  "
      ]
     },
     "execution_count": 210,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 211,
   "id": "4457d259",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "array([40, 86, 28, 70, 15, 19, 60, 51, 70,  4, 45, 67, 58, 39, 12, 42,  8,\n",
       "        8, 84, 29])"
      ]
     },
     "execution_count": 211,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "np.random.randint(1, 100, size=20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 212,
   "id": "b36ebe26",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "dtype([('name', '<U2'), ('age', 'i1')])"
      ]
     },
     "execution_count": 212,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "t = np.dtype([('name', 'U2'), ('age', 'i1')])\n",
    "t"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a711620d",
   "metadata": {},
   "source": [
    "# 变换后多个变量存储在一列中"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 213,
   "id": "da4881e2",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['country', 'year', 'm014', 'm1524', 'f014', 'f1425']\n"
     ]
    }
   ],
   "source": [
    "\n",
    "def_column2 = ['country','year', 'm014', 'm1524', 'f014', 'f1425']\n",
    "print(def_column2, )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 214,
   "id": "138a68e6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>m014</th>\n",
       "      <th>m1524</th>\n",
       "      <th>f014</th>\n",
       "      <th>f1425</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>71</td>\n",
       "      <td>55</td>\n",
       "      <td>40</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>26</td>\n",
       "      <td>66</td>\n",
       "      <td>51</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>58</td>\n",
       "      <td>34</td>\n",
       "      <td>50</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  country  year  m014  m1524  f014  f1425\n",
       "0      AD  2000    71     55    40     47\n",
       "1      AD  2000    26     66    51     13\n",
       "2      AD  2000    58     34    50     87"
      ]
     },
     "execution_count": 214,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df2 = pd.DataFrame(np.random.randint(1,100,18).reshape(3,6), index=[np.arange(3)], columns=def_column2)\n",
    "df2['year']=2000\n",
    "df2['country'] = 'AD'\n",
    "df2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "id": "e330f36b",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>country</th>\n",
       "      <th>year</th>\n",
       "      <th>sex_and_age</th>\n",
       "      <th>case</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m014</td>\n",
       "      <td>71</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m014</td>\n",
       "      <td>26</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m014</td>\n",
       "      <td>58</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m1524</td>\n",
       "      <td>55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m1524</td>\n",
       "      <td>66</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>m1524</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f014</td>\n",
       "      <td>40</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f014</td>\n",
       "      <td>51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f014</td>\n",
       "      <td>50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f1425</td>\n",
       "      <td>47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f1425</td>\n",
       "      <td>13</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>AD</td>\n",
       "      <td>2000</td>\n",
       "      <td>f1425</td>\n",
       "      <td>87</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   country  year sex_and_age  case\n",
       "0       AD  2000        m014    71\n",
       "1       AD  2000        m014    26\n",
       "2       AD  2000        m014    58\n",
       "3       AD  2000       m1524    55\n",
       "4       AD  2000       m1524    66\n",
       "5       AD  2000       m1524    34\n",
       "6       AD  2000        f014    40\n",
       "7       AD  2000        f014    51\n",
       "8       AD  2000        f014    50\n",
       "9       AD  2000       f1425    47\n",
       "10      AD  2000       f1425    13\n",
       "11      AD  2000       f1425    87"
      ]
     },
     "execution_count": 215,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df4=pd.melt(df2, id_vars=['country','year'], value_name=\"case\", var_name=\"sex_and_age\")\n",
    "df4"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "id": "89b4d861",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>sex</th>\n",
       "      <th>age_lower</th>\n",
       "      <th>age_upper</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>m</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>m</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>m</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>m</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>m</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>m</td>\n",
       "      <td>15</td>\n",
       "      <td>24</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>f</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>f</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>f</td>\n",
       "      <td>0</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>f</td>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>f</td>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>f</td>\n",
       "      <td>14</td>\n",
       "      <td>25</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   sex age_lower age_upper\n",
       "0    m         0        14\n",
       "1    m         0        14\n",
       "2    m         0        14\n",
       "3    m        15        24\n",
       "4    m        15        24\n",
       "5    m        15        24\n",
       "6    f         0        14\n",
       "7    f         0        14\n",
       "8    f         0        14\n",
       "9    f        14        25\n",
       "10   f        14        25\n",
       "11   f        14        25"
      ]
     },
     "execution_count": 216,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tmp_df=df4['sex_and_age'].str.extract(\"(\\D)(\\d+)(\\d{2})\",expand=False)\n",
    "tmp_df.columns=['sex', 'age_lower', 'age_upper']\n",
    "tmp_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "id": "4d9b3fe0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   sex age_lower age_upper    age\n",
      "0    m         0        14   0-14\n",
      "1    m         0        14   0-14\n",
      "2    m         0        14   0-14\n",
      "3    m        15        24  15-24\n",
      "4    m        15        24  15-24\n",
      "5    m        15        24  15-24\n",
      "6    f         0        14   0-14\n",
      "7    f         0        14   0-14\n",
      "8    f         0        14   0-14\n",
      "9    f        14        25  14-25\n",
      "10   f        14        25  14-25\n",
      "11   f        14        25  14-25\n",
      "   country  year sex_and_age  case sex age_lower age_upper    age\n",
      "0       AD  2000        m014    71   m         0        14   0-14\n",
      "1       AD  2000        m014    26   m         0        14   0-14\n",
      "2       AD  2000        m014    58   m         0        14   0-14\n",
      "3       AD  2000       m1524    55   m        15        24  15-24\n",
      "4       AD  2000       m1524    66   m        15        24  15-24\n",
      "5       AD  2000       m1524    34   m        15        24  15-24\n",
      "6       AD  2000        f014    40   f         0        14   0-14\n",
      "7       AD  2000        f014    51   f         0        14   0-14\n",
      "8       AD  2000        f014    50   f         0        14   0-14\n",
      "9       AD  2000       f1425    47   f        14        25  14-25\n",
      "10      AD  2000       f1425    13   f        14        25  14-25\n",
      "11      AD  2000       f1425    87   f        14        25  14-25\n",
      "   country  year  case sex    age\n",
      "0       AD  2000    71   m   0-14\n",
      "1       AD  2000    26   m   0-14\n",
      "2       AD  2000    58   m   0-14\n",
      "3       AD  2000    55   m  15-24\n",
      "4       AD  2000    66   m  15-24\n",
      "5       AD  2000    34   m  15-24\n",
      "6       AD  2000    40   f   0-14\n",
      "7       AD  2000    51   f   0-14\n",
      "8       AD  2000    50   f   0-14\n",
      "9       AD  2000    47   f  14-25\n",
      "10      AD  2000    13   f  14-25\n",
      "11      AD  2000    87   f  14-25\n"
     ]
    }
   ],
   "source": [
    "tmp_df['age']=tmp_df['age_lower']+'-'+tmp_df['age_upper']\n",
    "print(tmp_df)\n",
    "df4=pd.concat([df4,tmp_df],axis=1)\n",
    "print(df4)\n",
    "df5=df4.drop(['sex_and_age','age_lower', 'age_upper'],axis=1)\n",
    "df5.sort_values(ascending=True, by=['country', 'year', 'sex', 'age'])\n",
    "print(df5)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 172,
   "id": "8d5f4832",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "C:\\Users\\Administrator\\AppData\\Local\\Temp\\ipykernel_12308\\764320144.py:1: FutureWarning: ['country', 'age'] did not aggregate successfully. If any error is raised this will raise in a future version of pandas. Drop these columns/ops to avoid this warning.\n",
      "  df5.groupby('sex').agg(['mean', 'sum'])\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"2\" halign=\"left\">year</th>\n",
       "      <th colspan=\"2\" halign=\"left\">case</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "      <th>mean</th>\n",
       "      <th>sum</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>sex</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>f</th>\n",
       "      <td>2000.0</td>\n",
       "      <td>12000</td>\n",
       "      <td>46.166667</td>\n",
       "      <td>277</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>m</th>\n",
       "      <td>2000.0</td>\n",
       "      <td>12000</td>\n",
       "      <td>52.500000</td>\n",
       "      <td>315</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       year              case     \n",
       "       mean    sum       mean  sum\n",
       "sex                               \n",
       "f    2000.0  12000  46.166667  277\n",
       "m    2000.0  12000  52.500000  315"
      ]
     },
     "execution_count": 172,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df5.groupby('sex').agg(['mean', 'sum'])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "92e4d046",
   "metadata": {},
   "source": [
    "# 变量既在列中存储，又在行中存储"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "id": "3d570de3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>d1</th>\n",
       "      <th>d2</th>\n",
       "      <th>d3</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>2.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  year  month element   d1  d2   d3\n",
       "0  001  2010      1    tmax  2.0 NaN  NaN\n",
       "1  001  2010      1    tmin  1.0 NaN  3.0\n",
       "2  001  2010      2    tmax  NaN NaN  NaN"
      ]
     },
     "execution_count": 249,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def_b_columns=['id','year','month','element']\n",
    "c = ['d1','d2','d3']\n",
    "d = ['id','year','month','element']\n",
    "d.extend(c)\n",
    "c.extend(def_b_columns)\n",
    "df_b=pd.DataFrame([['001', 2010, 1, 'tmax', 2,np.nan,np.nan],\n",
    "                   ['001', 2010, 1, 'tmin',1,np.nan,3],\n",
    "                   ['001', 2010, 2, 'tmax',np.nan,np.nan,np.nan]],\n",
    "                  index=[np.arange(3)], columns=d)\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "id": "36b430b3",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>day_raw</th>\n",
       "      <th>value</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>2.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d3</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>001</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    id  year  month element day_raw  value\n",
       "0  001  2010      1    tmax      d1    2.0\n",
       "1  001  2010      1    tmin      d1    1.0\n",
       "2  001  2010      2    tmax      d1    NaN\n",
       "3  001  2010      1    tmax      d2    NaN\n",
       "4  001  2010      1    tmin      d2    NaN\n",
       "5  001  2010      2    tmax      d2    NaN\n",
       "6  001  2010      1    tmax      d3    NaN\n",
       "7  001  2010      1    tmin      d3    3.0\n",
       "8  001  2010      2    tmax      d3    NaN"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b=pd.melt(df_b, id_vars=def_b_columns,var_name='day_raw')\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 251,
   "id": "b4dbd30c",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>day_raw</th>\n",
       "      <th>value</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  year  month element day_raw  value day\n",
       "0  MX17004  2010      1    tmax      d1    2.0   1\n",
       "1  MX17004  2010      1    tmin      d1    1.0   1\n",
       "2  MX17004  2010      2    tmax      d1    NaN   1\n",
       "3  MX17004  2010      1    tmax      d2    NaN   2\n",
       "4  MX17004  2010      1    tmin      d2    NaN   2\n",
       "5  MX17004  2010      2    tmax      d2    NaN   2\n",
       "6  MX17004  2010      1    tmax      d3    NaN   3\n",
       "7  MX17004  2010      1    tmin      d3    3.0   3\n",
       "8  MX17004  2010      2    tmax      d3    NaN   3"
      ]
     },
     "execution_count": 251,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b['day']=df_b['day_raw'].str.extract('(\\d+)', expand=False)\n",
    "df_b['id']=\"MX17004\"\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 252,
   "id": "ffbaa413",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>year</th>\n",
       "      <th>month</th>\n",
       "      <th>element</th>\n",
       "      <th>day_raw</th>\n",
       "      <th>value</th>\n",
       "      <th>day</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d1</td>\n",
       "      <td>1.0</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>1</td>\n",
       "      <td>tmin</td>\n",
       "      <td>d3</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010</td>\n",
       "      <td>2</td>\n",
       "      <td>tmax</td>\n",
       "      <td>d3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id  year  month element day_raw  value  day\n",
       "0  MX17004  2010      1    tmax      d1    2.0    1\n",
       "1  MX17004  2010      1    tmin      d1    1.0    1\n",
       "2  MX17004  2010      2    tmax      d1    NaN    1\n",
       "3  MX17004  2010      1    tmax      d2    NaN    2\n",
       "4  MX17004  2010      1    tmin      d2    NaN    2\n",
       "5  MX17004  2010      2    tmax      d2    NaN    2\n",
       "6  MX17004  2010      1    tmax      d3    NaN    3\n",
       "7  MX17004  2010      1    tmin      d3    3.0    3\n",
       "8  MX17004  2010      2    tmax      d3    NaN    3"
      ]
     },
     "execution_count": 252,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b[['year', 'month', 'day']] = df_b[['year', 'month', 'day']].apply(lambda x:  pd.to_numeric(x, errors='ignore'))\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 253,
   "id": "12292614",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>id</th>\n",
       "      <th>element</th>\n",
       "      <th>value</th>\n",
       "      <th>date</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2010-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmin</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-01-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-02-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmin</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-01-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-02-02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmin</td>\n",
       "      <td>3.0</td>\n",
       "      <td>2010-01-03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>tmax</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2010-02-03</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        id element  value       date\n",
       "0  MX17004    tmax    2.0 2010-01-01\n",
       "1  MX17004    tmin    1.0 2010-01-01\n",
       "2  MX17004    tmax    NaN 2010-02-01\n",
       "3  MX17004    tmax    NaN 2010-01-02\n",
       "4  MX17004    tmin    NaN 2010-01-02\n",
       "5  MX17004    tmax    NaN 2010-02-02\n",
       "6  MX17004    tmax    NaN 2010-01-03\n",
       "7  MX17004    tmin    3.0 2010-01-03\n",
       "8  MX17004    tmax    NaN 2010-02-03"
      ]
     },
     "execution_count": 253,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "def create_date_from_year_month_day(row):\n",
    "    return datetime(year=row['year'],month=row['month'],day=row['day'])\n",
    "df_b['date']=df_b.apply(lambda row: create_date_from_year_month_day(row), axis=1)\n",
    "df = df_b.drop(['year', 'month','day','day_raw'],axis=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 272,
   "id": "16ed9b1a",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>element</th>\n",
       "      <th>tmax</th>\n",
       "      <th>tmin</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">MX17004</th>\n",
       "      <th>2010-01-01</th>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2010-01-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "element             tmax  tmin\n",
       "id      date                  \n",
       "MX17004 2010-01-01   2.0   1.0\n",
       "        2010-01-03   NaN   3.0"
      ]
     },
     "execution_count": 272,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 使用pivot_table函数拆分tmin,tmax\n",
    "df_b = df_b.dropna()\n",
    "\n",
    "df_b = df.pivot_table(index=['id','date'],columns='element', values='value')\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "b99ed1af",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bee4178d",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "raw",
   "id": "3359c00d",
   "metadata": {},
   "source": [
    "df_b.reset_index(drop=False, inplace=True)\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 274,
   "id": "430fbe32",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>element</th>\n",
       "      <th>id</th>\n",
       "      <th>date</th>\n",
       "      <th>tmax</th>\n",
       "      <th>tmin</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010-01-01</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MX17004</td>\n",
       "      <td>2010-01-03</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "element       id       date  tmax  tmin\n",
       "0        MX17004 2010-01-01   2.0   1.0\n",
       "1        MX17004 2010-01-03   NaN   3.0"
      ]
     },
     "execution_count": 274,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_b.dropna()\n",
    "df_b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2028f91e",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "a383a77f",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "5832e386",
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35fa7f5b",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
